<!-- build time:Tue Dec 17 2019 19:26:50 GMT+0800 (GMT+08:00) --><!doctype html><html class="theme-next mist" lang="zh-Hans"><head><meta name="generator" content="Hexo 3.8.0"><meta name="google-site-verification" content="7Tau9WyVgxnsEY9oYedu9g0U6_8akOX3wiKbaYcrg9A"><meta name="baidu-site-verification" content="EVwLiaxdxX"><link href="/css/xps13.css" rel="stylesheet" type="text/css"><link href="/css/message.css" rel="stylesheet" type="text/css"><script type="text/javascript" src="/js/jquery-1.11.3.min.js"></script><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=1"><meta http-equiv="Cache-Control" content="no-transform"><meta http-equiv="Cache-Control" content="no-siteapp"><link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css"><link href="/css/main.css?v=5.1.1" rel="stylesheet" type="text/css"><meta name="keywords" content="DataBase,Oracle,Oracle 11g,"><link rel="alternate" href="/atom.xml" title="MrBird" type="application/atom+xml"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.1.1"><meta name="description" content="oracle树查询的最重要的就是select…start with…connect by…prior语法了。依托于该语法，我们可以将一个表形结构的以树的顺序列出来。在下面列述了oracle中树型查询的常用查询方式以及经常使用的与树查询相关的oracle特性函数等，在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。1.准备测试表和测试数据："><meta name="keywords" content="DataBase,Oracle,Oracle 11g"><meta property="og:type" content="article"><meta property="og:title" content="Oracle树查询"><meta property="og:url" content="http://mrbird.cc/Oracle树查询.html"><meta property="og:site_name" content="MrBird"><meta property="og:description" content="oracle树查询的最重要的就是select…start with…connect by…prior语法了。依托于该语法，我们可以将一个表形结构的以树的顺序列出来。在下面列述了oracle中树型查询的常用查询方式以及经常使用的与树查询相关的oracle特性函数等，在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。1.准备测试表和测试数据："><meta property="og:locale" content="zh-Hans"><meta property="og:updated_time" content="2019-10-28T12:14:46.260Z"><meta name="twitter:card" content="summary"><meta name="twitter:title" content="Oracle树查询"><meta name="twitter:description" content="oracle树查询的最重要的就是select…start with…connect by…prior语法了。依托于该语法，我们可以将一个表形结构的以树的顺序列出来。在下面列述了oracle中树型查询的常用查询方式以及经常使用的与树查询相关的oracle特性函数等，在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。1.准备测试表和测试数据："><script type="text/javascript" id="hexo.configurations">var NexT=window.NexT||{},CONFIG={root:"/",scheme:"Mist",sidebar:{position:"left",display:"always",offset:12,offset_float:0,b2t:!1,scrollpercent:!1},fancybox:!1,motion:!1}</script><title>Oracle树查询 | MrBird</title></head><body ondragstart="return!1" class="animsition" lang="zh-Hans" style="overflow-x:hidden;padding-right:280px"><script type="text/javascript" src="/js/mo.min.js"></script><style>@media (min-width:768px) and (max-width:991px){body .header-innerr{width:700px!important}}.header-innerr{margin:0 auto;padding:100px 0 70px;width:880px}@media (min-width:1600px){.container .header-innerr{width:1200px}}.header-innerr{position:relative}.header-innerr{padding:20px 0 0}.header-innerr:after,.header-innerr:before{content:" ";display:table}.header-innerr:after{clear:both}@media (max-width:767px){.header-innerr{width:auto;padding:10px;margin-bottom:-20px}}</style><div class="container sidebar-position-left page-post-detail"><div class="headband"></div><header id="header" class="header"><div class="header-inner"><div class="site-brand-wrapper"><div class="site-meta"><link href="https://fonts.font.im/css?family=Merienda" rel="stylesheet"><div class="custom-logo-site-title"><a href="/" class="brand" rel="start"><span class="logo-line-before"><i></i></span> <span class="site-title" style="font-family:Merienda;font-size:1.3rem">MrBird</span> <span class="logo-line-after"><i></i></span></a></div><p class="site-subtitle"></p></div><div class="site-nav-toggle"><button><span class="btn-bar"></span> <span class="btn-bar"></span> <span class="btn-bar"></span></button></div></div><nav class="site-nav"><ul id="menu" class="menu"><li class="menu-item menu-item-home"><a href="/" rel="section">HOME</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section">ARCHIVES</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section">TAGS</a></li><li class="menu-item menu-item-friends"><a href="/friends/" rel="section">FRIENDS</a></li><div class="sidebar-toggle" style="display:none"><div class="sidebar-toggle-line-wrap"><span class="sidebar-toggle-line sidebar-toggle-line-first"></span> <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span> <span class="sidebar-toggle-line sidebar-toggle-line-last"></span></div></div></ul><div class="site-search"><div class="popup search-popup local-search-popup"><div class="local-search-header clearfix"><span class="search-icon"><i class="fa fa-search"></i> </span><span class="popup-btn-close"><i class="fa fa-times-circle"></i></span><div class="local-search-input-wrapper"><input autocomplete="off" placeholder="Search" spellcheck="false" type="text" id="local-search-input"></div></div><div id="local-search-result"></div></div></div></nav></div><div class="header-innerr"><div class="note info" style="margin:0;letter-spacing:.15px">🐤手把手教你搭建<strong>Spring Cloud微服务权限系统</strong>（从零到部署）：<a style="color:#40dab2;font-weight:600" href="https://www.kancloud.cn/mrbird/spring-cloud" target="_blank">https://www.kancloud.cn/mrbird/spring-cloud</a></div></div></header><main id="main" class="main"><div class="main-inner"><div class="content-wrap"><div id="content" class="content"><div id="posts" class="posts-expand"><article class="post post-type-normal" itemscope itemtype="http://schema.org/Article"><link itemprop="mainEntityOfPage" href="http://mrbird.cc/Oracle树查询.html"><span hidden itemprop="author" itemscope itemtype="http://schema.org/Person"><meta itemprop="name" content="MrBird"><meta itemprop="description" content=""><meta itemprop="image" content="/images/blogImage.jpg"></span><span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization"><meta itemprop="name" content="MrBird"></span><header class="post-header"><h1 class="post-title" itemprop="name headline">Oracle树查询</h1><div class="post-meta"><span class="post-time"><span class="post-meta-item-icon"><i class="fa fa-calendar-o"></i> </span><span class="post-meta-item-text">Posted on</span> <time title="创建于" itemprop="dateCreated datePublished" datetime="2016-03-28T19:32:28+08:00">2016-03-28 </time></span><span></span> <span class="post-meta-divider">|</span> <span class="page-pv"><i class="fa fa-laptop"></i>&nbsp;&nbsp;Visit count <span class="busuanzi-value" id="busuanzi_value_page_pv"></span></span></div></header><div class="post-body" itemprop="articleBody"><p>oracle树查询的最重要的就是select…start with…connect by…prior语法了。依托于该语法，我们可以将一个表形结构的以树的顺序列出来。在下面列述了oracle中树型查询的常用查询方式以及经常使用的与树查询相关的oracle特性函数等，在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。</p><p>1.准备测试表和测试数据：<a id="more"></a></p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--菜单目录结构表</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> tb_menu(</span><br><span class="line">   <span class="keyword">id</span>     <span class="built_in">number</span>(<span class="number">10</span>) <span class="keyword">not</span> <span class="literal">null</span>, <span class="comment">--主键id</span></span><br><span class="line">   title  varchar2(<span class="number">50</span>), <span class="comment">--标题</span></span><br><span class="line">   <span class="keyword">parent</span> <span class="built_in">number</span>(<span class="number">10</span>) <span class="comment">--parent id</span></span><br><span class="line">)</span><br><span class="line"> </span><br><span class="line"><span class="comment">--父菜单</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">1</span>, <span class="string">'父菜单1'</span>,<span class="number">0</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">2</span>, <span class="string">'父菜单2'</span>,<span class="number">0</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">3</span>, <span class="string">'父菜单3'</span>,<span class="number">0</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">4</span>, <span class="string">'父菜单4'</span>,<span class="number">0</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">5</span>, <span class="string">'父菜单5'</span>,<span class="number">0</span>);</span><br><span class="line"><span class="comment">--一级菜单</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">6</span>, <span class="string">'一级菜单6'</span>,<span class="number">1</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">7</span>, <span class="string">'一级菜单7'</span>,<span class="number">1</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">8</span>, <span class="string">'一级菜单8'</span>,<span class="number">1</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">9</span>, <span class="string">'一级菜单9'</span>,<span class="number">2</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">10</span>, <span class="string">'一级菜单10'</span>,<span class="number">2</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">11</span>, <span class="string">'一级菜单11'</span>,<span class="number">2</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">12</span>, <span class="string">'一级菜单12'</span>,<span class="number">3</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">13</span>, <span class="string">'一级菜单13'</span>,<span class="number">3</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">14</span>, <span class="string">'一级菜单14'</span>,<span class="number">3</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">15</span>, <span class="string">'一级菜单15'</span>,<span class="number">4</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">16</span>, <span class="string">'一级菜单16'</span>,<span class="number">4</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">17</span>, <span class="string">'一级菜单17'</span>,<span class="number">4</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">18</span>, <span class="string">'一级菜单18'</span>,<span class="number">5</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">19</span>, <span class="string">'一级菜单19'</span>,<span class="number">5</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">20</span>, <span class="string">'一级菜单20'</span>,<span class="number">5</span>);</span><br><span class="line"><span class="comment">--二级菜单</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">21</span>, <span class="string">'二级菜单21'</span>,<span class="number">6</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">22</span>, <span class="string">'二级菜单22'</span>,<span class="number">6</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">23</span>, <span class="string">'二级菜单23'</span>,<span class="number">7</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">24</span>, <span class="string">'二级菜单24'</span>,<span class="number">7</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">25</span>, <span class="string">'二级菜单25'</span>,<span class="number">8</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">26</span>, <span class="string">'二级菜单26'</span>,<span class="number">9</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">27</span>, <span class="string">'二级菜单27'</span>,<span class="number">10</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">28</span>, <span class="string">'二级菜单28'</span>,<span class="number">11</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">29</span>, <span class="string">'二级菜单29'</span>,<span class="number">12</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">30</span>, <span class="string">'二级菜单30'</span>,<span class="number">13</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">31</span>, <span class="string">'二级菜单31'</span>,<span class="number">14</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">32</span>, <span class="string">'二级菜单32'</span>,<span class="number">15</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">33</span>, <span class="string">'二级菜单33'</span>,<span class="number">16</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">34</span>, <span class="string">'二级菜单34'</span>,<span class="number">17</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">35</span>, <span class="string">'二级菜单35'</span>,<span class="number">18</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">36</span>, <span class="string">'二级菜单36'</span>,<span class="number">19</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">37</span>, <span class="string">'二级菜单37'</span>,<span class="number">20</span>);</span><br><span class="line"><span class="comment">--三级菜单</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">38</span>, <span class="string">'三级菜单38'</span>,<span class="number">21</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">39</span>, <span class="string">'三级菜单39'</span>,<span class="number">22</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">40</span>, <span class="string">'三级菜单40'</span>,<span class="number">23</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">41</span>, <span class="string">'三级菜单41'</span>,<span class="number">24</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">42</span>, <span class="string">'三级菜单42'</span>,<span class="number">25</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">43</span>, <span class="string">'三级菜单43'</span>,<span class="number">26</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">44</span>, <span class="string">'三级菜单44'</span>,<span class="number">27</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">45</span>, <span class="string">'三级菜单45'</span>,<span class="number">28</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">46</span>, <span class="string">'三级菜单46'</span>,<span class="number">28</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">47</span>, <span class="string">'三级菜单47'</span>,<span class="number">29</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">48</span>, <span class="string">'三级菜单48'</span>,<span class="number">30</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">49</span>, <span class="string">'三级菜单49'</span>,<span class="number">31</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tb_menu(<span class="keyword">id</span>, title, <span class="keyword">parent</span>) <span class="keyword">values</span>(<span class="number">50</span>, <span class="string">'三级菜单50'</span>,<span class="number">31</span>);</span><br><span class="line"><span class="keyword">commit</span>;</span><br></pre></td></tr></table></figure><p></p><p>parent字段存储的是上级id，如果是顶级父节点，该parent为0。</p><p>2.树操作</p><p>我们从最基本的操作，逐步列出树查询中常见的操作，所有查询出来的节点以家族中的辈份作比方。</p><h3 id="查找树中的所有顶级父节点"><a href="#查找树中的所有顶级父节点" class="headerlink" title="查找树中的所有顶级父节点"></a>查找树中的所有顶级父节点</h3><p>假设这个树是个目录结构，那么第一个操作总是找出所有的顶级节点，再根据该节点找到其下属节点。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select * from tb_menu m where m.parent=0;</span><br><span class="line"> </span><br><span class="line">        ID TITLE                                                  PARENT</span><br><span class="line"><span class="comment">---------- -------------------------------------------------- ----------</span></span><br><span class="line">         1 父菜单1                                                     0</span><br><span class="line">         2 父菜单2                                                     0</span><br><span class="line">         3 父菜单3                                                     0</span><br><span class="line">         4 父菜单4                                                     0</span><br><span class="line">         5 父菜单5                                                     0</span><br></pre></td></tr></table></figure><p></p><h3 id="查找一个节点的直属子节点"><a href="#查找一个节点的直属子节点" class="headerlink" title="查找一个节点的直属子节点"></a>查找一个节点的直属子节点</h3><p>如果查找的是直属子类节点，也是不用用到树型查询的。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select * from tb_menu m where m.parent=1;</span><br><span class="line"> </span><br><span class="line">        ID TITLE                                                  PARENT</span><br><span class="line"><span class="comment">---------- -------------------------------------------------- ----------</span></span><br><span class="line">         6 一级菜单6                                                   1</span><br><span class="line">         7 一级菜单7                                                   1</span><br><span class="line">         8 一级菜单8                                                   1</span><br></pre></td></tr></table></figure><p></p><h3 id="查找一个节点所有直属子节点"><a href="#查找一个节点所有直属子节点" class="headerlink" title="查找一个节点所有直属子节点"></a>查找一个节点所有直属子节点</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select * from tb_menu m start with m.id=1 connect by m.parent=prior m.id;</span><br><span class="line"> </span><br><span class="line">        ID TITLE                                                  PARENT</span><br><span class="line"><span class="comment">---------- -------------------------------------------------- ----------</span></span><br><span class="line">         1 父菜单1                                                     0</span><br><span class="line">         6 一级菜单6                                                   1</span><br><span class="line">        21 二级菜单21                                                  6</span><br><span class="line">        38 三级菜单38                                                 21</span><br><span class="line">        22 二级菜单22                                                  6</span><br><span class="line">        39 三级菜单39                                                 22</span><br><span class="line">         7 一级菜单7                                                   1</span><br><span class="line">        23 二级菜单23                                                  7</span><br><span class="line">        40 三级菜单40                                                 23</span><br><span class="line">        24 二级菜单24                                                  7</span><br><span class="line">        41 三级菜单41                                                 24</span><br><span class="line">         8 一级菜单8                                                   1</span><br><span class="line">        25 二级菜单25                                                  8</span><br><span class="line">        42 三级菜单42                                                 25</span><br><span class="line"> </span><br><span class="line">已选择14行。</span><br></pre></td></tr></table></figure><p>这个查找的是id为1的节点下的所有直属子类节点，包括子辈的和孙子辈的所有直属节点。</p><h3 id="查找一个节点的直属父节点"><a href="#查找一个节点的直属父节点" class="headerlink" title="查找一个节点的直属父节点"></a>查找一个节点的直属父节点</h3><p>如果查找的是节点的直属父节点，也是不用用到树型查询的。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    c. <span class="keyword">ID</span>,</span><br><span class="line">    c.title,</span><br><span class="line">    P . <span class="keyword">ID</span> parent_id,</span><br><span class="line">    P .title parent_title</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    tb_menu c,</span><br><span class="line">    tb_menu P</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">    c. <span class="keyword">PARENT</span> = P . <span class="keyword">ID</span></span><br><span class="line"><span class="keyword">AND</span> c. <span class="keyword">ID</span> = <span class="number">6</span>;</span><br><span class="line"> </span><br><span class="line">        ID TITLE                           PARENT_ID PARENT_TITLE</span><br><span class="line"><span class="comment">---------- --------------------- ---------- ---------------------------</span></span><br><span class="line">        6 一级菜单6                         1        父菜单1</span><br></pre></td></tr></table></figure><p></p><h3 id="查找一个节点所有直属父节点"><a href="#查找一个节点所有直属父节点" class="headerlink" title="查找一个节点所有直属父节点"></a>查找一个节点所有直属父节点</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select * from tb_menu m start with m.id=38 connect by prior m.parent=m.id;</span><br><span class="line"> </span><br><span class="line">        ID TITLE                                                  PARENT</span><br><span class="line"><span class="comment">---------- -------------------------------------------------- ----------</span></span><br><span class="line">        38 三级菜单38                                                 21</span><br><span class="line">        21 二级菜单21                                                  6</span><br><span class="line">         6 一级菜单6                                                   1</span><br><span class="line">         1 父菜单1                                                     0</span><br></pre></td></tr></table></figure><p>这里查找的就是id为1的所有直属父节点，打个比方就是找到一个人的父亲、祖父等。但是值得注意的是这个查询出来的结果的顺序是先列出子类节点再列出父类节点，姑且认为是个倒序吧。</p><p>上面列出两个树型查询方式，第3条语句和第5条语句，这两条语句之间的区别在于prior关键字的位置不同，所以决定了查询的方式不同。 当parent = prior id时，数据库会根据当前的id迭代出parent与该id相同的记录，所以查询的结果是迭代出了所有的子类记录；而prior parent = id时，数据库会跟据当前的parent来迭代出与当前的parent相同的id的记录，所以查询出来的结果就是所有的父类结果。</p><p>以下是一系列针对树结构的更深层次的查询，这里的查询不一定是最优的查询方式，或许只是其中的一种实现而已。</p><h3 id="查询一个节点的兄弟节点"><a href="#查询一个节点的兄弟节点" class="headerlink" title="查询一个节点的兄弟节点"></a>查询一个节点的兄弟节点</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--m.parent=m2.parent--&gt;同一个父亲</span></span><br><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    *   </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    tb_menu M</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">    <span class="keyword">EXISTS</span> (</span><br><span class="line">        <span class="keyword">SELECT</span></span><br><span class="line">            *</span><br><span class="line">        <span class="keyword">FROM</span></span><br><span class="line">            tb_menu m2</span><br><span class="line">        <span class="keyword">WHERE</span></span><br><span class="line">            M . <span class="keyword">PARENT</span> = m2. <span class="keyword">PARENT</span></span><br><span class="line">        <span class="keyword">AND</span> m2. <span class="keyword">ID</span> = <span class="number">6</span></span><br><span class="line">    );   </span><br><span class="line">    </span><br><span class="line">        ID TITLE                                                  PARENT</span><br><span class="line"><span class="comment">---------- -------------------------------------------------- ----------</span></span><br><span class="line">         8 一级菜单8                                                   1</span><br><span class="line">         7 一级菜单7                                                   1</span><br><span class="line">         6 一级菜单6                                                   1</span><br></pre></td></tr></table></figure><h3 id="查询与一个节点同级的节点"><a href="#查询与一个节点同级的节点" class="headerlink" title="查询与一个节点同级的节点"></a>查询与一个节点同级的节点</h3><p>如果在表中设置了级别的字段，那么在做这类查询时会很轻松，同一级别的就是与那个节点同级的，在这里列出不使用该字段时的实现：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">WITH</span> tmp <span class="keyword">AS</span> (</span><br><span class="line">    <span class="keyword">SELECT</span></span><br><span class="line">        A .*, <span class="keyword">LEVEL</span> leaf</span><br><span class="line">    <span class="keyword">FROM</span></span><br><span class="line">        tb_menu A <span class="keyword">START</span> <span class="keyword">WITH</span> A . <span class="keyword">PARENT</span> = <span class="number">0</span> <span class="keyword">CONNECT</span> <span class="keyword">BY</span> A . <span class="keyword">PARENT</span> = <span class="keyword">PRIOR</span> A . <span class="keyword">ID</span></span><br><span class="line">) <span class="keyword">SELECT</span></span><br><span class="line">    *</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    tmp</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">    leaf = (</span><br><span class="line">        <span class="keyword">SELECT</span></span><br><span class="line">            leaf</span><br><span class="line">        <span class="keyword">FROM</span></span><br><span class="line">            tmp</span><br><span class="line">        <span class="keyword">WHERE</span></span><br><span class="line">            <span class="keyword">ID</span> = <span class="number">50</span></span><br><span class="line">    ); </span><br><span class="line">    </span><br><span class="line">        ID TITLE                                        PARENT       LEAF</span><br><span class="line"><span class="comment">---------- ----------------------------------------- ---------- ----------</span></span><br><span class="line">        38 三级菜单38                                     21          4</span><br><span class="line">        39 三级菜单39                                     22          4</span><br><span class="line">        40 三级菜单40                                     23          4</span><br><span class="line">        41 三级菜单41                                     24          4</span><br><span class="line">        42 三级菜单42                                     25          4</span><br><span class="line">        43 三级菜单43                                     26          4</span><br><span class="line">        44 三级菜单44                                     27          4</span><br><span class="line">        45 三级菜单45                                     28          4</span><br><span class="line">        46 三级菜单46                                     28          4</span><br><span class="line">        47 三级菜单47                                     29          4</span><br><span class="line">        48 三级菜单48                                     30          4</span><br><span class="line">        49 三级菜单49                                     31          4</span><br><span class="line">        50 三级菜单50                                     31          4</span><br><span class="line"> </span><br><span class="line">已选择13行。</span><br></pre></td></tr></table></figure><p></p><p>这里使用两个技巧，一个是使用了level来标识每个节点在表中的级别，还有就是使用with语法模拟出了一张带有级别的临时表。</p><h3 id="查询一个节点父节点的兄弟节点"><a href="#查询一个节点父节点的兄弟节点" class="headerlink" title="查询一个节点父节点的兄弟节点"></a>查询一个节点父节点的兄弟节点</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">WITH</span> tmp <span class="keyword">AS</span> (</span><br><span class="line">    <span class="keyword">SELECT</span></span><br><span class="line">        tb_menu.*, <span class="keyword">LEVEL</span> lev</span><br><span class="line">    <span class="keyword">FROM</span></span><br><span class="line">        tb_menu <span class="keyword">START</span> <span class="keyword">WITH</span> <span class="keyword">PARENT</span> = <span class="number">0</span> <span class="keyword">CONNECT</span> <span class="keyword">BY</span> <span class="keyword">PARENT</span> = <span class="keyword">PRIOR</span> <span class="keyword">ID</span></span><br><span class="line">) <span class="keyword">SELECT</span></span><br><span class="line">    b.*</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    tmp b,</span><br><span class="line">    (</span><br><span class="line">        <span class="keyword">SELECT</span></span><br><span class="line">            *</span><br><span class="line">        <span class="keyword">FROM</span></span><br><span class="line">            tmp</span><br><span class="line">        <span class="keyword">WHERE</span></span><br><span class="line">            <span class="keyword">ID</span> = <span class="number">21</span></span><br><span class="line">        <span class="keyword">AND</span> lev = <span class="number">2</span></span><br><span class="line">    ) A</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">    b.lev = <span class="number">1</span></span><br><span class="line"><span class="keyword">UNION</span> ALL</span><br><span class="line">    <span class="keyword">SELECT</span></span><br><span class="line">        *</span><br><span class="line">    <span class="keyword">FROM</span></span><br><span class="line">        tmp</span><br><span class="line">    <span class="keyword">WHERE</span></span><br><span class="line">        <span class="keyword">PARENT</span> = (</span><br><span class="line">            <span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span></span><br><span class="line">                x. <span class="keyword">ID</span></span><br><span class="line">            <span class="keyword">FROM</span></span><br><span class="line">                tmp x,</span><br><span class="line">                <span class="comment">--祖父</span></span><br><span class="line">                tmp y,</span><br><span class="line">                <span class="comment">--父亲</span></span><br><span class="line">                (</span><br><span class="line">                    <span class="keyword">SELECT</span></span><br><span class="line">                        *</span><br><span class="line">                    <span class="keyword">FROM</span></span><br><span class="line">                        tmp</span><br><span class="line">                    <span class="keyword">WHERE</span></span><br><span class="line">                        <span class="keyword">ID</span> = <span class="number">21</span></span><br><span class="line">                    <span class="keyword">AND</span> lev &gt; <span class="number">2</span></span><br><span class="line">                ) z <span class="comment">--儿子</span></span><br><span class="line">            <span class="keyword">WHERE</span></span><br><span class="line">                y. <span class="keyword">ID</span> = z. <span class="keyword">PARENT</span></span><br><span class="line">            <span class="keyword">AND</span> x. <span class="keyword">ID</span> = y. <span class="keyword">PARENT</span></span><br><span class="line">        );</span><br><span class="line"> </span><br><span class="line"> </span><br><span class="line">        ID TITLE                                      PARENT        LEV</span><br><span class="line"><span class="comment">---------- ---------------------------------------- ---------- ----------</span></span><br><span class="line">         6 一级菜单6                                    1          2</span><br><span class="line">         7 一级菜单7                                    1          2</span><br><span class="line">         8 一级菜单8                                    1          2</span><br></pre></td></tr></table></figure><p>这里查询分成以下几步。</p><p>首先，将第7个一样，将全表都使用临时表加上级别；</p><p>其次，根据级别来判断有几种类型，以上文中举的例子来说，有三种情况：</p><p>（1）当前节点为顶级节点，即查询出来的lev值为1，那么它没有上级节点，不予考虑。</p><p>（2）当前节点为2级节点，查询出来的lev值为2，那么就只要保证lev级别为1的就是其上级节点的兄弟节点。</p><p>（3）其它情况就是3以及以上级别，那么就要选查询出来其上级的上级节点（祖父），再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。</p><p>最后，就是使用union将查询出来的结果进行结合起来，形成结果集。</p><h3 id="查询一个节点父节点的同级节点"><a href="#查询一个节点父节点的同级节点" class="headerlink" title="查询一个节点父节点的同级节点"></a>查询一个节点父节点的同级节点</h3><p>这个其实跟第7种情况是相同的。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">WITH</span> tmp <span class="keyword">AS</span> (</span><br><span class="line">    <span class="keyword">SELECT</span></span><br><span class="line">        A .*, <span class="keyword">LEVEL</span> leaf</span><br><span class="line">    <span class="keyword">FROM</span></span><br><span class="line">    tb_menu A <span class="keyword">START</span> <span class="keyword">WITH</span> A . <span class="keyword">PARENT</span> = <span class="number">0</span> <span class="keyword">CONNECT</span> <span class="keyword">BY</span> A . <span class="keyword">PARENT</span> = <span class="keyword">PRIOR</span> A . <span class="keyword">ID</span></span><br><span class="line">) <span class="keyword">SELECT</span></span><br><span class="line">    *</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    tmp</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">    leaf = (<span class="keyword">SELECT</span> leaf <span class="keyword">FROM</span> tmp <span class="keyword">WHERE</span> <span class="keyword">ID</span> = <span class="number">6</span>) - <span class="number">1</span>;  </span><br><span class="line">    </span><br><span class="line">        ID TITLE                                         PARENT       LEAF</span><br><span class="line"><span class="comment">---------- ------------------------------------------- ---------- ----------</span></span><br><span class="line">         1 父菜单1                                          0          1</span><br><span class="line">         2 父菜单2                                          0          1</span><br><span class="line">         3 父菜单3                                          0          1</span><br><span class="line">         4 父菜单4                                          0          1</span><br><span class="line">         5 父菜单5                                          0          1</span><br></pre></td></tr></table></figure><p></p><p>基本上，常见的查询在里面了，不常见的也有部分了。其中，查询的内容都是节点的基本信息，都是数据表中的基本字段，但是在树查询中还有些特殊需求，是对查询数据进行了处理的，常见的包括列出树路径等。</p><p>补充一个概念，对于数据库来说，根节点并不一定是在数据库中设计的顶级节点，对于数据库来说，根节点就是start with开始的地方。</p><p>下面列出的是一些与树相关的特殊需求。</p><h3 id="名称要列出名称全部路径"><a href="#名称要列出名称全部路径" class="headerlink" title="名称要列出名称全部路径"></a>名称要列出名称全部路径</h3><p>这里常见的有两种情况，一种是从顶级列出，直到当前节点的名称（或者其它属性）；一种是从当前节点列出，直到顶级节点的名称（或其它属性）。举地址为例：国内的习惯是从省开始、到市、到县、到居委会的，而国外的习惯正好相反。 从顶部开始：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    SYS_CONNECT_BY_PATH (title, <span class="string">'/'</span>)</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    tb_menu</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">    <span class="keyword">ID</span> = <span class="number">50</span> <span class="keyword">START</span> <span class="keyword">WITH</span> <span class="keyword">PARENT</span> = <span class="number">0</span> <span class="keyword">CONNECT</span> <span class="keyword">BY</span> <span class="keyword">PARENT</span> = <span class="keyword">PRIOR</span> <span class="keyword">ID</span>;</span><br><span class="line">    </span><br><span class="line">SYS_CONNECT_BY_PATH(TITLE,'/')</span><br><span class="line"><span class="comment">----------------------------------------------------</span></span><br><span class="line">/父菜单3/一级菜单14/二级菜单31/三级菜单50</span><br></pre></td></tr></table></figure><p></p><p>从当前节点开始：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    SYS_CONNECT_BY_PATH (title, <span class="string">'/'</span>)</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    tb_menu <span class="keyword">START</span> <span class="keyword">WITH</span> <span class="keyword">ID</span> = <span class="number">50</span> <span class="keyword">CONNECT</span> <span class="keyword">BY</span> <span class="keyword">PRIOR</span> <span class="keyword">PARENT</span> = <span class="keyword">ID</span>;</span><br><span class="line">    </span><br><span class="line">SYS_CONNECT_BY_PATH(TITLE,'/')</span><br><span class="line"><span class="comment">------------------------------------------------------    </span></span><br><span class="line">/三级菜单50</span><br><span class="line">/三级菜单50/二级菜单31</span><br><span class="line">/三级菜单50/二级菜单31/一级菜单14</span><br><span class="line">/三级菜单50/二级菜单31/一级菜单14/父菜单3</span><br></pre></td></tr></table></figure><p></p><p>在这里我又不得不放个牢骚了。oracle只提供了一个sys_connect_by_path函数，却忘了字符串的连接的顺序。在上面的例子中，第一个sql是从根节点开始遍历，而第二个sql是直接找到当前节点，从效率上来说已经是千差万别，更关键的是第一个sql只能选择一个节点，而第二个sql却是遍历出了一颗树来。再次ps一下。</p><p>sys_connect_by_path函数就是从start with开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串，这个功能还是很强大的。</p><h3 id="列出当前节点的根节点。"><a href="#列出当前节点的根节点。" class="headerlink" title="列出当前节点的根节点。"></a>列出当前节点的根节点。</h3><p>在前面说过，根节点就是start with开始的地方。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">CONNECT_BY_ROOT</span> title,</span><br><span class="line">    tb_menu.*</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    tb_menu <span class="keyword">START</span> <span class="keyword">WITH</span> <span class="keyword">ID</span> = <span class="number">50</span> <span class="keyword">CONNECT</span> <span class="keyword">BY</span> <span class="keyword">PRIOR</span> <span class="keyword">PARENT</span> = <span class="keyword">ID</span>;</span><br><span class="line"> </span><br><span class="line">CONNECT_BY_ROOTTITLE        ID TITLE                       PARENT</span><br><span class="line"><span class="comment">----------------------------------------------------------------</span></span><br><span class="line">三级菜单50                  50 三级菜单50                     31</span><br><span class="line">三级菜单50                  31 二级菜单31                     14</span><br><span class="line">三级菜单50                  14 一级菜单14                     3</span><br><span class="line">三级菜单50                  3 父菜单3                         0</span><br></pre></td></tr></table></figure><p></p><p>connect_by_root函数用来列的前面，记录的是当前节点的根节点的内容。</p><h3 id="列出当前节点是否为叶子"><a href="#列出当前节点是否为叶子" class="headerlink" title="列出当前节点是否为叶子"></a>列出当前节点是否为叶子</h3><p>这个比较常见，尤其在动态目录中，在查出的内容是否还有下级节点时，这个函数是很适用的。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">CONNECT_BY_ISLEAF</span>,</span><br><span class="line">    tb_menu.*</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    tb_menu <span class="keyword">START</span> <span class="keyword">WITH</span> <span class="keyword">PARENT</span> = <span class="number">0</span> <span class="keyword">CONNECT</span> <span class="keyword">BY</span> <span class="keyword">PARENT</span> = <span class="keyword">PRIOR</span> <span class="keyword">ID</span>;</span><br><span class="line"> </span><br><span class="line">CONNECT_BY_ISLEAF         ID TITLE                                     PARENT</span><br><span class="line"><span class="comment">----------------- ---------- --------------------------------------- ----------</span></span><br><span class="line">                0          1 父菜单1                                     0</span><br><span class="line">                0          6 一级菜单6                                   1</span><br><span class="line">                0         21 二级菜单21                                  6</span><br><span class="line">                1         38 三级菜单38                                  21</span><br><span class="line">                0         22 二级菜单22                                  6</span><br><span class="line">                1         39 三级菜单39                                  22</span><br><span class="line">                0          7 一级菜单7                                   1</span><br><span class="line">                0         23 二级菜单23                                  7</span><br><span class="line">                1         40 三级菜单40                                  23</span><br><span class="line">                0         24 二级菜单24                                  7</span><br><span class="line">                1         41 三级菜单41                                  24</span><br><span class="line">                0          8 一级菜单8                                   1</span><br><span class="line">                0         25 二级菜单25                                  8</span><br><span class="line">                1         42 三级菜单42                                  25</span><br><span class="line">                0          2 父菜单2                                     0</span><br><span class="line">                0          9 一级菜单9                                   2</span><br><span class="line">                0         26 二级菜单26                                  9</span><br><span class="line">                1         43 三级菜单43                                  6</span><br><span class="line">                0         10 一级菜单10                                  2</span><br><span class="line">                0         27 二级菜单27                                  10</span><br><span class="line">                1         44 三级菜单44                                  27</span><br><span class="line">                0         11 一级菜单11                                  2</span><br><span class="line">                0         28 二级菜单28                                  11</span><br><span class="line">                1         45 三级菜单45                                  28</span><br><span class="line">                1         46 三级菜单46                                  28</span><br><span class="line">                0          3 父菜单3                                     0</span><br><span class="line">                0         12 一级菜单12                                  3</span><br><span class="line">                0         29 二级菜单29                                  12</span><br><span class="line">                1         47 三级菜单47                                  29</span><br><span class="line">                0         13 一级菜单13                                  3</span><br><span class="line">                0         30 二级菜单30                                  13</span><br><span class="line">                1         48 三级菜单48                                  30</span><br><span class="line">                0         14 一级菜单14                                  3</span><br><span class="line">                0         31 二级菜单31                                  14</span><br><span class="line">                1         49 三级菜单49                                  31</span><br><span class="line">                1         50 三级菜单50                                  31</span><br><span class="line">                0          4 父菜单4                                     0</span><br><span class="line">                0         15 一级菜单15                                  4</span><br><span class="line">                1         32 二级菜单32                                  15</span><br><span class="line">                0         16 一级菜单16                                  4</span><br><span class="line">                1         33 二级菜单33                                  16</span><br><span class="line">                0         17 一级菜单17                                  4</span><br><span class="line">                1         34 二级菜单34                                  17</span><br><span class="line">                0          5 父菜单5                                     0</span><br><span class="line">                0         18 一级菜单18                                  5</span><br><span class="line">                1         35 二级菜单35                                  18</span><br><span class="line">                0         19 一级菜单19                                  5</span><br><span class="line">                1         36 二级菜单36                                  19</span><br><span class="line">                0         20 一级菜单20                                  5</span><br><span class="line">                1         37 二级菜单37                                  20</span><br><span class="line"> </span><br><span class="line">已选择50行。</span><br></pre></td></tr></table></figure><p></p><script>$(".post-body a").not(".thispage").addClass("ignore-href").attr("target","_blank")</script></div><div></div><div><div style="padding:10px 0;margin:20px auto;width:90%;text-align:center;color:#878787"><div>请作者喝瓶肥宅水~</div><button id="rewardButton" style="margin-top:10px" disable="enable" onclick='var e=document.getElementById("QR");"none"===e.style.display?e.style.display="block":e.style.display="none"'><span style="height:46px;width:46px;line-height:46px;border-radius:50%;color:#fe5f55;font-weight:600;background:#ffd5be;border:none;box-shadow:0 4px 8px 0 rgba(255,213,190,.4)">￥</span></button><div id="QR" style="display:none"><div id="wechat" style="display:inline-block"><img id="wechat_qr" src="/img/wechat_pay.png" alt="MrBird WeChat Pay"></div><div id="alipay" style="display:inline-block"><img id="alipay_qr" src="/img/ali_pay.png" alt="MrBird Alipay"></div></div></div><style>#QR img{width:auto;margin:.8em 1em 0 1em}</style></div><div><ul class="post-copyright"><li class="post-copyright-author"><strong>本文作者：</strong> MrBird</li><li class="post-copyright-link"><strong>本文链接：</strong> <a href="http://mrbird.cc/Oracle树查询.html" title="Oracle树查询">http://mrbird.cc/Oracle树查询.html</a></li><li class="post-copyright-license"><strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" rel="external nofollow" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明出处！</li></ul></div><footer class="post-footer"><div class="post-tags" style="margin-bottom:1.3rem"><a href="/tags/DataBase/" rel="tag"># DataBase</a> <a href="/tags/Oracle/" rel="tag"># Oracle</a> <a href="/tags/Oracle-11g/" rel="tag"># Oracle 11g</a></div><div class="post-nav"><div class="post-nav-next post-nav-item"><a href="/使用JFinal快速搭建-Java-web项目.html" rel="next" title="使用JFinal快速搭建 Java web项目"><i class="fa fa-chevron-left"></i> 使用JFinal快速搭建 Java web项目</a></div><span class="post-nav-divider"></span><div class="post-nav-prev post-nav-item"><a href="/上传下载demo.html" rel="prev" title="上传下载demo">上传下载demo <i class="fa fa-chevron-right"></i></a></div></div></footer></article><hr><div id="container"></div><div class="post-spread"><div id="comment-div"></div><style>.valine .vlist{margin-bottom:3rem}.valine .vwrap .vcontrol .col.col-60{text-align:left}.valine .vlist .vcard .vhead,.valine .vlist .vcard section .vfooter{text-align:left}.valine .vlist .vcard section{padding-bottom:.5rem!important}.vname{color:#42b983!important}.valine .vinfo .col{text-align:left;margin-left:-27rem}div#comment-div{margin-bottom:-8rem}.valine .vlist .vcard .vcontent .code,.valine .vlist .vcard .vcontent code,.valine .vlist .vcard .vcontent pre{background:#fbfbfb}.valine .vlist .vcard .vcontent a{color:#42b983}.valine .vlist .vcard .vimg{border-radius:3px}.valine .vbtn{border-radius:2px;padding:.3rem 1.25rem}.valine .vbtn:active,.valine .vbtn:hover{color:#42b983;border-color:#42b983;background-color:#fff}.valine .vwrap .vheader .vinput:focus{border-bottom-color:#42b983}.valine .vlist .vcard .vcontent.expand:before{background:-webkit-gradient(linear,left top,left bottom,from(hsla(0,0%,100%,0)),to(hsla(0,0%,100%,.2)));background:linear-gradient(180deg,hsla(0,0%,100%,0),hsla(0,0%,100%,.2))}.valine .vlist .vcard .vcontent.expand:after{content:"点击展开";font-size:.4rem;text-align:right;left:-1rem;background:hsla(0,0%,100%,.2)}.valine .vlist .vcard section .vfooter .vat{color:#b3b3b3}.valine .vlist .vcard section .vfooter .vat:hover{color:#42b983}.vcontent img{margin:0}.valine .info{display:none}</style><script type="text/javascript" src="/js/av.min.js"></script><script type="text/javascript" src="/js/Valine.min.js"></script><script type="text/javascript" src="/js/activate-power-mode.js"></script><script>POWERMODE.colorful=!0,POWERMODE.shake=!1,document.body.addEventListener("input",POWERMODE),new Valine({el:"#comment-div",notify:!1,verify:!0,appId:"SMcDFP1bN1jgb9Lo8JmtICHm-gzGzoHsz",appKey:"dH4nrUrt3V5XiJiI6Qyejnbi",placeholder:"",path:window.location.pathname,avatar:"monsterid",guest_info:["nick","mail","link"]});var chicken='<a href="#"><img src="https://image.uisdc.com/wp-content/uploads/2018/06/uisdc-chat-chicken.gif" class="checken"></a>';$("#comment-div").prepend(chicken)</script></div></div><script>var $bqinline=$("img[alt='bq-inline']");$bqinline.css({width:"2.3rem",height:"2.3rem",display:"inline","vertical-align":"text-bottom"})</script></div><div class="comments" id="comments"></div></div><aside id="sidebar" class="sidebar" onselectstart="return!1"><div class="sidebar-inner"><ul class="sidebar-nav motion-element"><li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">Contents</li><li class="sidebar-nav-overview" data-target="site-overview">Site Preview</li></ul><section class="site-overview sidebar-panel"><div class="sidebar-sticky sticky"><div itemscope itemtype="http://schema.org/Person"><div class="author__avatar"><img src="/images/blogImage.jpg" class="author__avatar" alt="MrBird" itemprop="image"></div><div class="author__content"><h3 class="author__name" itemprop="name">MrBird's Blog</h3><p class="author__bio" itemprop="description">A simple blog, code repository, just keep blogging</p></div><div class="author__urls-wrapper"><button class="btn btn--inverse">Follow</button><ul class="author__urls social-icons"><li><a href="http://map.baidu.com/?newmap=1&s=s%26wd%3D%E7%A6%8F%E5%B7%9E%E5%B8%82%26c%3D300&from=alamap&tpl=mapcity" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-map-marker" aria-hidden="true"></i>&nbsp;&nbsp;FuZhou,CN</a></li><li><a href="https://love.mrbird.cc" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-diamond" aria-hidden="true"></i>&nbsp;&nbsp;Love</a></li><li><a href="https://cloud.mrbird.cn" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-chain" aria-hidden="true"></i>&nbsp;&nbsp;FEBS</a></li><li><a href="/atom.xml" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-rss" aria-hidden="true"></i>&nbsp;&nbsp;RSS</a></li><li><a href="https://gitee.com/mrbirdd" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-codepen" aria-hidden="true"></i>&nbsp;&nbsp;Gitee</a></li><li><a href="https://github.com/wuyouzhuguli" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-github-alt" aria-hidden="true"></i>&nbsp;&nbsp;GitHub</a></li><li><a href="javascript:;" class="popup-trigger"><i class="fa fa-fw fa-search" aria-hidden="true"></i>&nbsp;&nbsp;Search</a></li></ul></div></div></div><script>var $urls=$(".author__urls").find("a");$urls.each(function(){var o=$(this);o.mouseenter(function(){o.css({color:"#414547"})}),o.mouseleave(function(){o.css({color:""})})})</script></section><section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active"><div class="post-toc"><div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-3"><a class="nav-link" href="#查找树中的所有顶级父节点"><span class="nav-number">1.</span> <span class="nav-text">查找树中的所有顶级父节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查找一个节点的直属子节点"><span class="nav-number">2.</span> <span class="nav-text">查找一个节点的直属子节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查找一个节点所有直属子节点"><span class="nav-number">3.</span> <span class="nav-text">查找一个节点所有直属子节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查找一个节点的直属父节点"><span class="nav-number">4.</span> <span class="nav-text">查找一个节点的直属父节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查找一个节点所有直属父节点"><span class="nav-number">5.</span> <span class="nav-text">查找一个节点所有直属父节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查询一个节点的兄弟节点"><span class="nav-number">6.</span> <span class="nav-text">查询一个节点的兄弟节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查询与一个节点同级的节点"><span class="nav-number">7.</span> <span class="nav-text">查询与一个节点同级的节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查询一个节点父节点的兄弟节点"><span class="nav-number">8.</span> <span class="nav-text">查询一个节点父节点的兄弟节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查询一个节点父节点的同级节点"><span class="nav-number">9.</span> <span class="nav-text">查询一个节点父节点的同级节点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#名称要列出名称全部路径"><span class="nav-number">10.</span> <span class="nav-text">名称要列出名称全部路径</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#列出当前节点的根节点。"><span class="nav-number">11.</span> <span class="nav-text">列出当前节点的根节点。</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#列出当前节点是否为叶子"><span class="nav-number">12.</span> <span class="nav-text">列出当前节点是否为叶子</span></a></li></ol></div></div></section></div></aside></div></main><footer id="footer" class="footer" onselectstart="return!1"><div class="footer-inner"><div class="copyright">&copy; 2016 - <span itemprop="copyrightYear">2019</span>&nbsp;&nbsp; <span class="author" itemprop="copyrightHolder">MrBird</span>&nbsp;&nbsp;|<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>&nbsp;&nbsp;UV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_uv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span> &nbsp;&nbsp;PV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_pv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span></div></div></footer><div class="back-to-top"><span style="font-family:'Source Sans Pro','Helvetica Neue',Arial,sans-serif;font-size:1.2em;font-weight:600">TOP</span></div></div><script type="text/javascript">"[object Function]"!==Object.prototype.toString.call(window.Promise)&&(window.Promise=null)</script><script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script><script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script><script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script><script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script><script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script><script type="text/javascript" src="/js/src/utils.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/motion.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/post-details.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.1"></script><script type="text/javascript">function proceedsearch(){$("body").append('<div class="search-popup-overlay local-search-pop-overlay"></div>').css("overflow","hidden"),$(".search-popup-overlay").click(onPopupClose),$(".popup").toggle();var t=$("#local-search-input");t.attr("autocapitalize","none"),t.attr("autocorrect","off"),t.focus()}var isfetched=!1,isXml=!0,search_path="search.xml";0===search_path.length?search_path="search.xml":search_path.endsWith("json")&&(isXml=!1);var path="/"+search_path,onPopupClose=function(t){$(".popup").hide(),$("#local-search-input").val(""),$(".search-result-list").remove(),$("#no-result").remove(),$(".local-search-pop-overlay").remove(),$("body").css("overflow","")},searchFunc=function(t,e,o){"use strict";$("body").append('<div class="search-popup-overlay local-search-pop-overlay"><div id="search-loading-icon"><i class="fa fa-spinner fa-pulse fa-2x fa-fw"></i></div></div>').css("overflow","hidden"),$("#search-loading-icon").css("margin","20% auto 0 auto").css("text-align","center"),$.ajax({url:t,dataType:isXml?"xml":"json",async:!0,success:function(t){isfetched=!0,$(".popup").detach().appendTo(".header-inner");var n=isXml?$("entry",t).map(function(){return{title:$("title",this).text(),content:$("content",this).text(),url:$("url",this).text()}}).get():t,r=document.getElementById(e),s=document.getElementById(o),a=function(){var t=r.value.trim().toLowerCase(),e=t.split(/[\s\-]+/);e.length>1&&e.push(t);var o=[];if(t.length>0&&n.forEach(function(n){function r(e,o,n,r){for(var s=r[r.length-1],a=s.position,i=s.word,l=[],h=0;a+i.length<=n&&0!=r.length;){i===t&&h++,l.push({position:a,length:i.length});var p=a+i.length;for(r.pop();0!=r.length&&(s=r[r.length-1],a=s.position,i=s.word,p>a);)r.pop()}return c+=h,{hits:l,start:o,end:n,searchTextCount:h}}function s(t,e){var o="",n=e.start;return e.hits.forEach(function(e){o+=t.substring(n,e.position);var r=e.position+e.length;o+='<b class="search-keyword">'+t.substring(e.position,r)+"</b>",n=r}),o+=t.substring(n,e.end)}var a=!1,i=0,c=0,l=n.title.trim(),h=l.toLowerCase(),p=n.content.trim().replace(/<[^>]+>/g,""),u=p.toLowerCase(),f=decodeURIComponent(n.url),d=[],g=[];if(""!=l&&(e.forEach(function(t){function e(t,e,o){var n=t.length;if(0===n)return[];var r=0,s=[],a=[];for(o||(e=e.toLowerCase(),t=t.toLowerCase());(s=e.indexOf(t,r))>-1;)a.push({position:s,word:t}),r=s+n;return a}d=d.concat(e(t,h,!1)),g=g.concat(e(t,u,!1))}),(d.length>0||g.length>0)&&(a=!0,i=d.length+g.length)),a){[d,g].forEach(function(t){t.sort(function(t,e){return e.position!==t.position?e.position-t.position:t.word.length-e.word.length})});var v=[];0!=d.length&&v.push(r(l,0,l.length,d));for(var C=[];0!=g.length;){var $=g[g.length-1],m=$.position,x=$.word,w=m-20,y=m+80;w<0&&(w=0),y<m+x.length&&(y=m+x.length),y>p.length&&(y=p.length),C.push(r(p,w,y,g))}C.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hits.length!==e.hits.length?e.hits.length-t.hits.length:t.start-e.start});var T=parseInt("1");T>=0&&(C=C.slice(0,T));var b="";b+=0!=v.length?"<li><a href='"+f+"' class='search-result-title'>"+s(l,v[0])+"</a>":"<li><a href='"+f+"' class='search-result-title'>"+l+"</a>",C.forEach(function(t){b+="<a href='"+f+'\'><p class="search-result">'+s(p,t)+"...</p></a>"}),b+="</li>",o.push({item:b,searchTextCount:c,hitCount:i,id:o.length})}}),1===e.length&&""===e[0])s.innerHTML='<div id="no-result"><i class="fa fa-search fa-5x" /></div>';else if(0===o.length)s.innerHTML='<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>';else{o.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hitCount!==e.hitCount?e.hitCount-t.hitCount:e.id-t.id});var a='<ul class="search-result-list">';o.forEach(function(t){a+=t.item}),a+="</ul>",s.innerHTML=a}};r.addEventListener("input",a),$(".local-search-pop-overlay").remove(),$("body").css("overflow",""),proceedsearch()}})};$(".popup-trigger").click(function(t){t.stopPropagation(),isfetched===!1?searchFunc(path,"local-search-input","local-search-result"):proceedsearch()}),$(".popup-btn-close").click(onPopupClose),$(".popup").click(function(t){t.stopPropagation()}),$(document).on("keyup",function(t){var e=27===t.which&&$(".search-popup").is(":visible");e&&onPopupClose()})</script></body><script>$(function(){$("a").not(".nav-link,.cloud-tie-join-count,.ignore-href,.jstree-anchor").addClass("animsition-link")});var burst1=new mojs.Burst({left:0,top:0,radius:{5:40},children:{shape:"circle",fill:["red","cyan","orange"],fillOpacity:.8,radiusX:3.5,radiusY:3.5}});document.addEventListener("click",function(a){null==a.target.href&&"footer"!=a.target.className&&"copyright"!=a.target.className&&"author__urls social-icons"!=a.target.className&&"author__avatar"!=a.target.className&&"sidebar sidebar-active"!=a.target.className&&burst1.tune({x:a.pageX,y:a.pageY}).generate().replay()})</script><script type="text/javascript" src="/js/message.js"></script></html><!-- rebuild by neat -->